package eu.jba.denshijisho;

import java.util.Date;
import java.util.List;

import android.os.Bundle;
import android.widget.TextView;
import eu.jba.denshijisho.dao.CardDao;
import eu.jba.denshijisho.dao.StatDao;
import eu.jba.denshijisho.entity.Card;
import eu.jba.denshijisho.exceptionhandler.ExceptionHandler;
import eu.jba.denshijisho.util.Util;

public class OverallStatActivity extends AbstractDenshiActivity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		Thread.setDefaultUncaughtExceptionHandler( new ExceptionHandler( this ));
		setContentView(R.layout.activity_overall_stat);
		
		final CardDao cardDao = new CardDao(this);
		cardDao.open();
		final Integer wordCount = cardDao.getRowCount("Word");
		final Integer phraseCount = cardDao.getRowCount("Phrase");
		((TextView)findViewById(R.id.word_count_value)).setText( wordCount.toString() );
		((TextView)findViewById(R.id.phrase_count_value)).setText( phraseCount.toString());
		((TextView)findViewById(R.id.quiz_question_today_value)).setText( questionTodayCount(cardDao).toString() );
		final Date today = new Date();
		final Date tomorow3 = Util.addDate( new Date(), 0, 0, 3);
		((TextView)findViewById(R.id.quiz_question_3_value)).setText( questionCountForDates(cardDao, today, tomorow3).toString() );
		final Date date10 = Util.addDate(new Date(), 0, 0, 10);
		((TextView)findViewById(R.id.quiz_question_10_value)).setText(
			questionCountForDates(cardDao, tomorow3, date10).toString()
		);
		((TextView)findViewById(R.id.quiz_question_11_value)).setText(
			greaterThanCount(cardDao, Util.addDate(new Date(), 0, 0, 10)).toString()
		);
		cardDao.close();
		((TextView)findViewById(R.id.quiz_total_question_value)).setText(
				countSum( StatDao.KEY_REPS ).toString()
			);
		((TextView)findViewById(R.id.quiz_total_right_answer_value)).setText(
				countSum( StatDao.KEY_OK ).toString()
			);
		((TextView)findViewById(R.id.quiz_total_wrong_answer_value)).setText(
				countSum( StatDao.KEY_WRONG ).toString()
			);
	}
	
	private Integer countSum(String columnId){
		final StringBuilder sb = new StringBuilder();
		sb.append("select sum(").append(columnId).append(") from ")
		  .append(this.getString( R.database.STAT_TABLE ) ).append(";");
		final StatDao statDao = new StatDao( this );
		statDao.open();
		Integer sum = (Integer) statDao.rawQuery( sb.toString() );
		statDao.close();
		return sum;
	}
	
	private Integer questionTodayCount(CardDao cardDao){
		final StringBuilder sb = new StringBuilder();
		sb.append( "select * from " ).append( this.getString( R.database.CARD_TABLE ) ).append(" a,")
		  .append(this.getString( R.database.STAT_TABLE )).append(" b").append(" where a.").append(CardDao.KEY_STAT_ID)
		  .append(" = b.").append( StatDao.KEY_ROWID ).append( " and ").append("date(nextDate) <= date('")
		  .append( Util.dateToString( new Date() ) ).append( "')").append(";");
		final List<Card> cards = cardDao.runRawQuery( sb.toString() );
		return cards.size();
	}

	private Integer greaterThanCount(CardDao cardDao, Date date){
		final StringBuilder sb = new StringBuilder();
		sb.append( "select * from " ).append( this.getString( R.database.CARD_TABLE ) ).append(" a,")
		  .append(this.getString( R.database.STAT_TABLE )).append(" b").append(" where a.").append(CardDao.KEY_STAT_ID)
		  .append(" = b.").append( StatDao.KEY_ROWID ).append( " and ").append("date(nextDate) > date('")
		  .append( Util.dateToString( date ) ).append( "')").append(";");
		final List<Card> cards = cardDao.runRawQuery( sb.toString() );
		return cards.size();
	}
	
	private Integer questionCountForDates(CardDao cardDao, Date date1, Date date2){
		final StringBuilder sb = new StringBuilder();
		sb.append( "select * from " ).append( this.getString( R.database.CARD_TABLE ) ).append(" a,")
		  .append(this.getString( R.database.STAT_TABLE )).append(" b").append(" where a.").append(CardDao.KEY_STAT_ID)
		  .append(" = b.").append( StatDao.KEY_ROWID ).append( " and ").append("date(nextDate) > date('")
		  .append( Util.dateToString( date1 ) ).append( "') and ").append("date(nextDate) <= date('").append(Util.dateToString( date2 )).append("')")
		  .append(";");
		final List<Card> cards = cardDao.runRawQuery( sb.toString() );
		return cards.size();
	}
}
